/*
 *所有关于EQU_TOOL类的业务代码应在此处编写
*可使用repository.调用常用方法，获取EF/Dapper等信息
*如果需要事务请使用repository.DbContextBeginTransaction
*也可使用DBServerProvider.手动获取数据库相关信息
*用户信息、权限、角色等使用UserContext.Current操作
*EQU_TOOLService对增、删、改查、导入、导出、审核业务代码扩展参照ServiceFunFilter
*/
using VOL.Core.BaseProvider;
using VOL.Core.Extensions.AutofacManager;
using VOL.Entity.DomainModels;
using System.Linq;
using VOL.Core.Utilities;
using System.Linq.Expressions;
using VOL.Core.Extensions;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.AspNetCore.Http;
using VOL.System.IRepositories;
using System;
using System.Collections.Generic;
using System.Net;
using OfficeOpenXml.FormulaParsing.Excel.Functions.DateTime;
using VOL.Core.ManageUser;
using Confluent.Kafka;
using NPOI.HPSF;
using NPOI.Util;
using StackExchange.Redis;

namespace VOL.System.Services
{
    public partial class EQU_TOOLService
    {
        private readonly IHttpContextAccessor _httpContextAccessor;
        private readonly IEQU_TOOLRepository _repository;//访问数据库

        [ActivatorUtilitiesConstructor]
        public EQU_TOOLService(
            IEQU_TOOLRepository dbRepository,
            IHttpContextAccessor httpContextAccessor
            )
        : base(dbRepository)
        {
            _httpContextAccessor = httpContextAccessor;
            _repository = dbRepository;
            //多租户会用到这init代码，其他情况可以不用
            //base.Init(dbRepository);
        }
        WebResponseContent _webResponse = new WebResponseContent();
        public override PageGridData<EQU_TOOL> GetPageData(PageDataOptions options)
        {
            if (options.Value != null && options.Value.ToString() == "2")//(整刀组装
            {

                //string userName = "admin";
                ////FromSqlInterpolated方法参数化查询，具体参照EF文档
                //FormattableString formattableString = $"select * from Sys_User where UserName={userName}";

                //var user = await repository.FromSqlInterpolated(formattableString)
                //        .Where(x => 1 == 1)
                //        .Select(s => new
                //        {
                //            s.User_Id,
                //            s.UserPwd
                //        }).FirstOrDefaultAsync();
                //return user;
                //QuerySql = $@"SELECT t1.*
                //            FROM EQU_TOOL t1
                //            JOIN (
                //                SELECT Identifier
                //                FROM EQU_TOOL
                //                GROUP BY Identifier
                //            ) t2 ON t1.Identifier = t2.Identifier
                //            WHERE
                //         1=1
                //            and t1.RfidCode is  null
                //            and
                //             (
                //                SELECT COUNT(*)
                //                FROM EQU_TOOL t3
                //                WHERE t3.Identifier = t1.Identifier
                //                AND (t3.id <= t1.id OR t3.program_seq < t1.program_seq)
                //            ) <= 3";
              //  if(options.Order=="")
                //{
               
               // }
                string TwoQuerySql = $@"
						select [MinToolLoad],[MaxToolLoad],[ToolSpeed], [MeasureStartTime],[MeasureEndTime], [FileSize], [ExecuteTaskDiffTime], [IsUse],[IsChange],
						[Id]
						,CAST( t0.rn as int) as [TogetherSeq]
						,[program_seq]
      ,[Img]
      ,[Identifier]
      ,[Holder_Code]
      ,[HolderBD]
      ,[HolderSD]
      ,[HolderL]
      ,[HolderBDTheory]
      ,[HolderSDTheory]
      ,[CTheory]
      ,[CReality]
      ,[Accessorie1Code]
      ,[Ace1C]
      ,[Ace1D]
      ,[Ace1L1]
      ,[Ace1L]
      ,[Ace1DTheory]
      ,[Ace1LTheory]
      ,[Accessorie2Code]
      ,[Ace2C]
      ,[Ace2D]
      ,[Ace2L1]
      ,[Ace2L]
      ,[Ace2DTheory]
      ,[Ace2LTheory]
      ,[Accessorie3_Code]
      ,[Ace3C]
      ,[Ace3D]
      ,[Ace3L1]
      ,[Ace3L]
      ,[Ace3DTheory]
      ,[Ace3LTheory]
      ,[work_order_id]
      ,[tool_name]
      ,[R]
      ,[HolderLTheory]
      ,[knife_length]
      ,[reality_knife_length]
      ,[blade_length]
      ,[reality_blade_length]
      ,[collet_length]
      ,[extensions_length]
      ,[pairing_length]
      ,[knife_width]
      ,[reality_knife_width]
      ,[accuracy]
      ,[category]
      ,[extensions_type]
      ,[collet_type]
      ,[tool_type]
      ,[handle_type]
      ,[location]
      ,[reality_pairing_length]
      ,[reality_extensions_length]
      ,[reality_R]
      ,[vendor_code]
      ,[tool_materials]
      ,[process_start_time]
      ,[process_end_time]
      ,[process_before_tool_width]
      ,[process_after_tool_width]
      ,[process_before_tool_length]
      ,[process_after_tool_length]
      ,[use_status]
      ,[work_order_status]
      
      ,[SHELVES_Id]
      ,[RfidCode]
      ,[MeasureTemplate]
      ,[time]
      ,[JacketLength]
      ,[RotationalSpeed]
      ,[Feed]
      ,[CoolingMethod]
      ,[Hardness]
      ,[SecornSeq]
      ,[AccuracyAfterUse]
      ,[HolderTypeId]
      ,[IsTogether]
      ,[IsMeasure]
      ,[IsTotallengthRange]
      ,[IsKnifeLengthRange]
      ,[IsKnifeDRange]
      ,[IsHolderLenghtRange]
      ,[IsHolderRRange]
      ,[IsAce1LenghtRange]
      ,[IsAce1DRange]
      ,[IsAce2LenghtRange]
      ,[IsAce2DRange]
      ,[IsAce3LenghtRange]
      ,[IsAce3DRange]
      ,[CreateBy]
      ,[CreateOn]
      ,[UpdateBy]
      ,[LastUpdateOn]
      ,[IsDeleted]
						 from (
						select  ROW_NUMBER() over (partition by t1.Identifier order by t1.Identifier) as rn ,t1.* from EQU_TOOL t1
						--where t1.RfidCode is  null
						)  t0 
						
					 "  ;
                if(options.Wheres.Contains("Identifier"))
                {
                    options.Order = "asc";
                    options.Sort = "program_seq";//"Identifier desc,program_seq";
                    QuerySql = TwoQuerySql;
                }else if(options.Wheres.Contains("HolderTypeId"))
                {
                    QuerySql = TwoQuerySql;
                }
                else if (options.Wheres.Contains("tool_name"))
                {
                    QuerySql = TwoQuerySql;
                }
                else if (options.Wheres.Contains("Holder_Code"))
                {
                    QuerySql = TwoQuerySql;
                }
                else if (options.Wheres.Contains("program_seq"))
                {
                    QuerySql = TwoQuerySql;
                }               
                else
                {
                    options.Order = "asc";
                   // options.Sort = "program_seq";//"Identifier desc,program_seq";
                    QuerySql = $@"

select [MinToolLoad],[MaxToolLoad],[ToolSpeed], [MeasureStartTime],[MeasureEndTime],[FileSize], [ExecuteTaskDiffTime], [IsUse],[IsChange],
						[Id]
						,CAST( t0.rn as int) as [TogetherSeq]
						,[program_seq]
      ,[Img]
      ,[Identifier]
      ,[Holder_Code]
      ,[HolderBD]
      ,[HolderSD]
      ,[HolderL]
      ,[HolderBDTheory]
      ,[HolderSDTheory]
      ,[CTheory]
      ,[CReality]
      ,[Accessorie1Code]
      ,[Ace1C]
      ,[Ace1D]
      ,[Ace1L1]
      ,[Ace1L]
      ,[Ace1DTheory]
      ,[Ace1LTheory]
      ,[Accessorie2Code]
      ,[Ace2C]
      ,[Ace2D]
      ,[Ace2L1]
      ,[Ace2L]
      ,[Ace2DTheory]
      ,[Ace2LTheory]
      ,[Accessorie3_Code]
      ,[Ace3C]
      ,[Ace3D]
      ,[Ace3L1]
      ,[Ace3L]
      ,[Ace3DTheory]
      ,[Ace3LTheory]
      ,[work_order_id]
      ,[tool_name]
      ,[R]
      ,[HolderLTheory]
      ,[knife_length]
      ,[reality_knife_length]
      ,[blade_length]
      ,[reality_blade_length]
      ,[collet_length]
      ,[extensions_length]
      ,[pairing_length]
      ,[knife_width]
      ,[reality_knife_width]
      ,[accuracy]
      ,[category]
      ,[extensions_type]
      ,[collet_type]
      ,[tool_type]
      ,[handle_type]
      ,[location]
      ,[reality_pairing_length]
      ,[reality_extensions_length]
      ,[reality_R]
      ,[vendor_code]
      ,[tool_materials]
      ,[process_start_time]
      ,[process_end_time]
      ,[process_before_tool_width]
      ,[process_after_tool_width]
      ,[process_before_tool_length]
      ,[process_after_tool_length]
      ,[use_status]
      ,[work_order_status]
      
      ,[SHELVES_Id]
      ,[RfidCode]
      ,[MeasureTemplate]
      ,[time]
      ,[JacketLength]
      ,[RotationalSpeed]
      ,[Feed]
      ,[CoolingMethod]
      ,[Hardness]
      ,[SecornSeq]
      ,[AccuracyAfterUse]
      ,[HolderTypeId]
      ,[IsTogether]
      ,[IsMeasure]
      ,[IsTotallengthRange]
      ,[IsKnifeLengthRange]
      ,[IsKnifeDRange]
      ,[IsHolderLenghtRange]
      ,[IsHolderRRange]
      ,[IsAce1LenghtRange]
      ,[IsAce1DRange]
      ,[IsAce2LenghtRange]
      ,[IsAce2DRange]
      ,[IsAce3LenghtRange]
      ,[IsAce3DRange]
      ,[CreateBy]
      ,[CreateOn]
      ,[UpdateBy]
      ,[LastUpdateOn]
      , [IsDeleted]
						 from (
						select  ROW_NUMBER() over (partition by t1.Identifier order by t1.Identifier) as rn ,t1.* from EQU_TOOL t1
						where t1.RfidCode is  null
						)  t0 
						where t0.rn<4
						  
						
					 ";
                }
                SummaryExpress = (IQueryable<EQU_TOOL> queryable) =>
                {
                    return queryable.GroupBy(x => 1).Select(x => new
                    {
                        //AvgPrice注意大小写和数据库字段大小写一样
                        Qty = x.Sum(o => o.ExecuteTaskDiffTime).ToString("f2")
                    })
                    .FirstOrDefault();
                };
                return base.GetPageData(options);
            }
            else
            {
                QuerySql = $@"select [MinToolLoad],[MaxToolLoad],[ToolSpeed], [MeasureStartTime],[MeasureEndTime],FileSize,ExecuteTaskDiffTime, v2.Status as IsUse,v1.Status as IsChange,
						[Id]
						,[TogetherSeq]
						,[program_seq]
      ,[Img]
      ,[Identifier]
      ,[Holder_Code]
      ,[HolderBD]
      ,[HolderSD]
      ,[HolderL]
      ,[HolderBDTheory]
      ,[HolderSDTheory]
      ,[CTheory]
      ,[CReality]
      ,[Accessorie1Code]
      ,[Ace1C]
      ,[Ace1D]
      ,[Ace1L1]
      ,[Ace1L]
      ,[Ace1DTheory]
      ,[Ace1LTheory]
      ,[Accessorie2Code]
      ,[Ace2C]
      ,[Ace2D]
      ,[Ace2L1]
      ,[Ace2L]
      ,[Ace2DTheory]
      ,[Ace2LTheory]
      ,[Accessorie3_Code]
      ,[Ace3C]
      ,[Ace3D]
      ,[Ace3L1]
      ,[Ace3L]
      ,[Ace3DTheory]
      ,[Ace3LTheory]
      ,[work_order_id]
      ,[tool_name]
      ,[R]
      ,[HolderLTheory]
      ,[knife_length]
      ,[reality_knife_length]
      ,[blade_length]
      ,[reality_blade_length]
      ,[collet_length]
      ,[extensions_length]
      ,[pairing_length]
      ,[knife_width]
      ,[reality_knife_width]
      ,[accuracy]
      ,[category]
      ,[extensions_type]
      ,[collet_type]
      ,[tool_type]
      ,[handle_type]
      ,[location]
      ,[reality_pairing_length]
      ,[reality_extensions_length]
      ,[reality_R]
      ,[vendor_code]
      ,[tool_materials]
      ,[process_start_time]
      ,[process_end_time]
      ,[process_before_tool_width]
      ,[process_after_tool_width]
      ,[process_before_tool_length]
      ,[process_after_tool_length]
      ,[use_status]
      ,[work_order_status]
      
      ,[SHELVES_Id]
      ,[RfidCode]
      ,[MeasureTemplate]
      ,[time]
      ,[JacketLength]
      ,[RotationalSpeed]
      ,[Feed]
      ,[CoolingMethod]
      ,[Hardness]
      ,[SecornSeq]
      ,[AccuracyAfterUse]
      ,[HolderTypeId]
      ,[IsTogether]
      ,[IsMeasure]
      ,[IsTotallengthRange]
      ,[IsKnifeLengthRange]
      ,[IsKnifeDRange]
      ,[IsHolderLenghtRange]
      ,[IsHolderRRange]
      ,[IsAce1LenghtRange]
      ,[IsAce1DRange]
      ,[IsAce2LenghtRange]
      ,[IsAce2DRange]
      ,[IsAce3LenghtRange]
      ,[IsAce3DRange]
      ,[CreateBy]
      ,[CreateOn]
      ,[UpdateBy]
      ,[LastUpdateOn]
      ,[IsDeleted]
						 from EQU_TOOL
						 left join (
						 select Status, sequence,t0.OrderCode from EQU_COM_DEFINITION_TASK  t0
left join PROC_PROGRAM t1 on t0.ProgramId=cast( t1.Id as nvarchar(50))
where 
isnull(t0.ProgramId,'')<>''
and t0.Command='TW004'
						 ) v1 on EQU_TOOL.Identifier=v1.OrderCode and EQU_TOOL.program_seq=v1.sequence
						 	 left join (
						 select Status, sequence,t0.OrderCode from EQU_COM_DEFINITION_TASK  t0
left join PROC_PROGRAM t1 on t0.ProgramId=cast( t1.Id as nvarchar(50))
where 
isnull(t0.ProgramId,'')<>''
and t0.Command='TW0041'
						 ) v2 on EQU_TOOL.Identifier=v2.OrderCode and EQU_TOOL.program_seq=v2.sequence";
                SummaryExpress = (IQueryable<EQU_TOOL> queryable) =>
                {
                    return queryable.GroupBy(x => 1).Select(x => new
                    {
                        //AvgPrice注意大小写和数据库字段大小写一样
                        ExecuteTaskDiffTime =DateTime.Now.ToString("yyyy:mm:dd") // x.Sum(o => o.ExecuteTaskDiffTime).ToString("f2")
                    })
                    .FirstOrDefault();
                };
                return base.GetPageData(options);
            }
           
        }
        public override WebResponseContent Update(SaveModel saveModel)
        {
            KeyValuePair<string, object> sHELVES_Id = saveModel.MainData.Where(t => t.Key == "SHELVES_Id").FirstOrDefault();
            KeyValuePair<string, object> location = saveModel.MainData.Where(t => t.Key == "location").FirstOrDefault();
            KeyValuePair<string, object> id = saveModel.MainData.Where(t => t.Key == "Id").FirstOrDefault();
            KeyValuePair<string, object> reality_pairing_length = saveModel.MainData.Where(t => t.Key == "reality_pairing_length").FirstOrDefault();
            if (sHELVES_Id.Key != null)
            {
                if (sHELVES_Id.Value != "")
                {
                    var shelvesId = Guid.Parse(sHELVES_Id.Value.ToString());
                    var idStr = Guid.Parse(id.Value.ToString());
                    var iNTE_SHELVES = _repository.DbContext.Set<INTE_SHELVES>()
                        .Where(t => t.INTE_SHELVES_Id == shelvesId).FirstOrDefault();
                    var equTool = _repository.DbContext.Set<EQU_TOOL>().Where(t => t.Id == idStr).FirstOrDefault();
                    var planwork = _repository.DbContext.Set<PLAN_WORK_ORDER>().Where(t => t.OrderCode == equTool.Identifier).FirstOrDefault();
                    var equ = _repository.DbContext.Set<EQU_EQUIPMENT>().Where(t => t.Description == planwork.processing_machines).FirstOrDefault();
                    iNTE_SHELVES.ToolId = equTool.Id.ToString().ToUpper();
                    if (equ.MaxKnifeLength <= decimal.Parse(reality_pairing_length.Value.ToString()))
                    {
                        return _webResponse.Error("刀的对刀长不在设备最大刀长范围，请检查!");
                    }
                    if (iNTE_SHELVES.HolderTypeId != null && equTool.HolderTypeId != null && equTool.HolderTypeId != iNTE_SHELVES.HolderTypeId)
                    {
                        return _webResponse.Error("存储点与刀柄类型不匹配，请检查!");
                    }

                    _repository.DbContext.Set<INTE_SHELVES>().Update(iNTE_SHELVES);
                    if (location.Key == null)
                    {
                        saveModel.MainData.Add("location", iNTE_SHELVES.DBCode);
                    }
                    else
                    {
                        saveModel.MainData["location"] = iNTE_SHELVES.DBCode;
                    }
                }
                else { saveModel.MainData["location"] = 0; }
            }

            return base.Update(saveModel);
        }
    }
}
